/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;


    import java.sql.*;
    import java.util.ArrayList;
    import Module.*;
/**

  @author Vg@rd
 */
public class ProjectController {
    private Database database = new Database();

    public int registerProject(Project project){
        
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "Insert into project values(DEFAULT,?,?,?)",autogen);
            statement.setString(1, project.getName());
            statement.setString(2, project.getDesc());
            statement.setString(3, project.getGoals());
            statement.executeUpdate();
            PreparedStatement statement2 = conn.prepareStatement(""
                    + "select projectId from project where name=? and description=? and goals=?");
            statement2.setString(1, project.getName());
            statement2.setString(2, project.getDesc());
            statement2.setString(3, project.getGoals());
            ResultSet rs = statement2.executeQuery();
            while(rs.next()){
                int id = rs.getInt(1);
                conn.close();
                return id;
            }

                return rs.getInt(1);
        }catch (Exception e) {
            System.err.println(e);

        }
        return 0;
    }
    public boolean editProject(Project project){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("Update project "
                    + "set name=?, description=?, goals=? where projectId =?");
            statement.setString(1, project.getName());
            statement.setString(2, project.getDesc());
            statement.setString(3, project.getGoals());
            statement.setInt(4, project.getId());
            statement.executeUpdate();
            conn.close();
            return true;
        }catch(Exception e){
            System.err.println(e);
        }

        return false;
    }

    /*
     * @TODO Make this method use deleteActivity etc.
     *
     * To delete a project:
     * Resource_project, Users_project, ActionList easy delete
     * Fetch sessionsToProject, delete decisionList, Chatt log and sessions
     * Fetch planningToProject delete planning
     * Delete activity
     * 
     */
    public boolean deleteProject(Project project){
        Connection conn = null;
        Controller controller = new Controller();
        try{
            conn = DriverManager.getConnection(database.getURL());
            //Delete all users set to a project in usersProject
            deleteUserstoProject(project);
            //Delete alle resources set to a project in resourcesProject
            controller.getResource().deleteResourcesToProject(project);
            //Delete actionList set to a project
            controller.getActionlist().deleteActionList(controller.getActionlist().getActionlist(project));
            //Delete experience set ta a project
            controller.getExperience().deleteExperience(controller.getExperience().getExperience(project));
            //Delete chattLog to all sessions belonging to a project
            ArrayList<Session> sessions = controller.getSession().getSessionsToProject(project);
            /*for(int i=0;i<sessions.size();i++){
                controller.getSession().deleteSession(sessions.get(i));
            }
            ArrayList<Planning> plannings = controller.getPlanning().getPlanningToProject(project);
            for(int i=0;i<plannings.size();i++){
                controller.getPlanning().deletePlanning(plannings.get(i));
            }*/
            ArrayList<Activity> activities = controller.getActivity().getActivitysToProject(project);
            for(int i=0;i<activities.size();i++){
                controller.getActivity().deleteActivity(activities.get(i));
            }
            PreparedStatement statement = conn.prepareStatement("delete from project where projectId=?");
            statement.setInt(1,project.getId());
            statement.executeUpdate();
            conn.close();
            return true;


        }catch(Exception e){
            System.err.println(e);
        }
        return false;
    }
    /*
     * Registrers users to a project
     */
/*
 * @TODO: må endre slik at rett rolle blir registrert
 */

    public boolean setUsersToProject(ArrayList<User> users, int id){

        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("Insert into usersProject values(?,?,?)");
            for(User u:users){
                s.setInt(1,u.getId());
                s.setInt(2,id);
                s.setInt(3, 0);
                s.executeUpdate();
            }
            conn.close();
            return true;

        }catch(Exception e){
            System.err.println(e);
        }
        return false;
    }
    /*
     * Delete in table usersProject whene a project is deleted.
     */

    public boolean deleteUserstoProject(Project p){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            int id = p.getId();
            PreparedStatement s = conn.prepareStatement("delete from usersProject where projectId=?");
            s.setInt(1, id);
            s.executeUpdate();
            conn.close();
            return true;

        }catch(Exception e){
            System.err.println(e);
        }

        return false;
    }
    /*
     * Return all users to a project
     *
     */
    public ArrayList<User> getUsersToProject(int p){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(""
                    + "select users.*, usersRole.roleId from users "+
                      "left join usersProject on(users.userID = usersProject.userId)"+
                      "left join project on (usersProject.projectId = project.projectId)"+
                      "left join usersRole on(users.userId = usersRole.userId)"+
                      "where project.projectId = ?");
            s.setInt(1, p);
            ResultSet r = s.executeQuery();
            while(r.next()){
               users.add(new User(r.getInt(1),r.getString(2),r.getString(3)));
            }

            conn.close();
            return users;
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /*
     * Returns all projects
     */

    public ArrayList<Project> getProjects(){
        Connection conn = null;
        ArrayList<Project> projects = new ArrayList<Project>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(""
                    + "select * from project");
            ResultSet r = s.executeQuery();
            while(r.next()){
                Project p = new Project(r.getInt(1),r.getString(2),r.getString(3),r.getString(4));
                projects.add(p);
            }

            conn.close();
            return projects;
        }catch(Exception e){
            System.err.println(e);
        }
        return projects;
    }
}
